package myPetStore.persistence.impl;

import myPetStore.domain.Account;
import myPetStore.persistence.AccountDao;

import java.sql.*;

import myPetStore.persistence.DBUtil;

public class AccountImpl implements AccountDao {

    private static final String GET_ACCOUNT_BY_USERNAME_AND_PASSWORD="SELECT \n" +
            "SIGNON.USERNAME,\n" +
            "ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,ACCOUNT.STATUS,\n" +
            "ACCOUNT.ADDR1 AS addresss1,ACCOUNT.ADDR2 AS address2,\n" +
            "ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,ACCOUNT.COUNTRY,ACCOUNT.PHONE,\n" +
            "PROFILE.LANGPREF AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId,\n" +
            "PROFILE.MYLISTOPT AS listOption,PROFILE.BANNEROPT AS bannerOption,\n" +
            "BANNERDATA.BANNERNAME \n" +
            "FROM ACCOUNT ,PROFILE, SIGNON, BANNERDATA \n" +
            "WHERE ACCOUNT.USERID=? AND SIGNON.PASSWORD =? \n" +
            "AND SIGNON.USERNAME=ACCOUNT.USERID \n" +
            "AND PROFILE.USERID =ACCOUNT.USERID \n" +
            "AND PROFILE.FAVCATEGORY=BANNERDATA.FAVCATEGORY";

    private static final String GET_ACCOUNT_BY_EMAIL="SELECT \n" +
            "SIGNON.USERNAME,\n" +
            "ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,ACCOUNT.STATUS,\n" +
            "ACCOUNT.ADDR1 AS addresss1,ACCOUNT.ADDR2 AS address2,\n" +
            "ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,ACCOUNT.COUNTRY,ACCOUNT.PHONE,\n" +
            "PROFILE.LANGPREF AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId,\n" +
            "PROFILE.MYLISTOPT AS listOption,PROFILE.BANNEROPT AS bannerOption,\n" +
            "BANNERDATA.BANNERNAME \n" +
            "FROM ACCOUNT ,PROFILE, SIGNON, BANNERDATA \n" +
            "WHERE ACCOUNT.EMAIL=? \n" +
            "AND SIGNON.USERNAME=ACCOUNT.USERID \n" +
            "AND PROFILE.USERID =ACCOUNT.USERID \n" +
            "AND PROFILE.FAVCATEGORY=BANNERDATA.FAVCATEGORY";

    private static final String GET_ACCOUNT_BY_USERNAME = "SELECT * FROM SIGNON WHERE SIGNON.USERNAME=?";
    private static final String INSERT_ACCOUNT=
            "INSERT INTO ACCOUNT (userid,email,firstname,lastname,status,addr1,addr2,city,state,zip,country,phone) " +
                    "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";

    private static final String INSERT_PROFILE="INSERT INTO PROFILE " +
            "(userid,langpref,favcategory,mylistopt,banneropt) " +
            "VALUES (?,?,?,?,?)";
    private static final String INSERT_SIGNON="INSERT INTO SIGNON " +
            "(username,password) VALUES (?,?)";
    private static final String UPDATE_ACCOUNT=
            "UPDATE ACCOUNT SET EMAIL = ?,FIRSTNAME = ?,LASTNAME = ?,STATUS = ?,ADDR1 = ?,ADDR2 = ?,CITY = ?,STATE = ?,ZIP = ?,COUNTRY = ?,PHONE = ?WHERE USERID = ?";

    private static final String UPDATE_PROFILE=
            "UPDATE PROFILE SET LANGPREF = ?,FAVCATEGORY = ?WHERE USERID=?";
    private static final String UPDATE_SIGNON=
            "UPDATE SIGNON SET PASSWORD = ? WHERE USERNAME = ?";
//    @Override
//    public Account getAccountByUsername(String username) {
//
//        return null;
//    }


    @Override
    public Account getAccountByUsernameAndPassword(Account account) {
        Account accountResult=null;
        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(GET_ACCOUNT_BY_USERNAME_AND_PASSWORD);
            preparedStatement.setString(1,account.getUsername());
            preparedStatement.setString(2,account.getPassword());
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                accountResult=this.resultSetToAccount(resultSet);
            }

        DBUtil.closeResultSet(resultSet);
        DBUtil.closePreparedStatement(preparedStatement);
        DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return accountResult;
    }
    @Override
    public boolean isUsernameExist(String username){
        Account accountResult=null;
        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(GET_ACCOUNT_BY_USERNAME);
            preparedStatement.setString(1,username);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                DBUtil.closeResultSet(resultSet);
                DBUtil.closePreparedStatement(preparedStatement);
                DBUtil.closeConnection(connection);
                return true;
            }

            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    @Override
    public Account getAccountByEmail(String email) {
        Account accountResult=null;
        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(GET_ACCOUNT_BY_EMAIL);
            preparedStatement.setString(1,email);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                accountResult=this.resultSetToAccount(resultSet);
            }

            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return accountResult;
    }

    @Override
    public void insertAccount(Account account) {
//        boolean result=false;
        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(INSERT_ACCOUNT);
            preparedStatement.setString(1,account.getUsername());
            preparedStatement.setString(2,account.getEmail());
            preparedStatement.setString(3,account.getFirstName());
            preparedStatement.setString(4,account.getLastName());
            preparedStatement.setString(5,account.getStatus());
            preparedStatement.setString(6,account.getAddress1());
            preparedStatement.setString(7,account.getAddress2());
            preparedStatement.setString(8,account.getCity());
            preparedStatement.setString(9,account.getState());
            preparedStatement.setString(10,account.getZip());
            preparedStatement.setString(11,account.getCountry());
            preparedStatement.setString(12,account.getPhone());
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        return result;
    }

    @Override
    public void insertProfile(Account account) {
        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(INSERT_PROFILE);
            preparedStatement.setString(1,account.getUsername());
            preparedStatement.setString(2,account.getLanguagePreference());
            preparedStatement.setString(3,account.getFavouriteCategoryId());
            preparedStatement.setInt(4,1);
            preparedStatement.setInt(5,1);
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void insertSignon(Account account) {
        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(INSERT_SIGNON);
            preparedStatement.setString(1,account.getUsername());
            preparedStatement.setString(2,account.getPassword());
            preparedStatement.executeUpdate();

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateAccount(Account account) {

        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(UPDATE_ACCOUNT);

            preparedStatement.setString(1,account.getEmail());
            preparedStatement.setString(2,account.getFirstName());
            preparedStatement.setString(3,account.getLastName());
            preparedStatement.setString(4,account.getStatus());
            preparedStatement.setString(5,account.getAddress1());
            preparedStatement.setString(6,account.getAddress2());
            preparedStatement.setString(7,account.getCity());
            preparedStatement.setString(8,account.getState());
            preparedStatement.setString(9,account.getZip());
            preparedStatement.setString(10,account.getCountry());
            preparedStatement.setString(11,account.getPhone());
            preparedStatement.setString(12,account.getUsername());
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateProfile(Account account) {
        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(UPDATE_PROFILE);

            preparedStatement.setString(1,account.getLanguagePreference());
            preparedStatement.setString(2,account.getFavouriteCategoryId());
            preparedStatement.setString(3,account.getUsername());
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateSignon(Account account) {
        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(UPDATE_SIGNON);
            preparedStatement.setString(1,account.getPassword());
            preparedStatement.setString(2,account.getUsername());
            preparedStatement.executeUpdate();

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Account resultSetToAccount(ResultSet resultSet) throws SQLException {
        Account accountreslut=new Account();
        accountreslut.setUsername(resultSet.getString(1));
        accountreslut.setEmail(resultSet.getString(2));
        accountreslut.setFirstName(resultSet.getString(3));
        accountreslut.setLastName(resultSet.getString(4));
        accountreslut.setStatus(resultSet.getString(5));
        accountreslut.setAddress1(resultSet.getString(6));
        accountreslut.setAddress2(resultSet.getString(7));
        accountreslut.setCity(resultSet.getString(8));
        accountreslut.setState(resultSet.getString(9));
        accountreslut.setZip(resultSet.getString(10));
        accountreslut.setCountry(resultSet.getString(11));
        accountreslut.setPhone(resultSet.getString(12));
        accountreslut.setLanguagePreference(resultSet.getString(13));
        accountreslut.setFavouriteCategoryId(resultSet.getString(14));
        accountreslut.setListOption(resultSet.getInt(15)==1);
        accountreslut.setBannerOption(resultSet.getInt(16)==1);
        accountreslut.setBannerName(resultSet.getString(17));

        return accountreslut;
    }

    public static void main(String[] args) {
        AccountDao accountDao=new AccountImpl();
        Account account=new Account();
        account.setUsername("user");
        account.setPassword("12");
        Account loginAccount=accountDao.getAccountByUsernameAndPassword(account);

        System.out.println(accountDao.isUsernameExist("123"));
    }
}
